//
//  Sqlite3DbManager.m
//  MClient
//
//  Created by  史正烨 on 10-11-22.
//  Copyright 2010 __MyCompanyName__. All rights reserved.
//

#import "Sqlite3DbManager.h"


@implementation Sqlite3DbManager

- (id)init {
	if (self = [super init]) {
		//占用200k
		_sqlBuf = malloc(1000);
	}
	return self;
}
- (void)dealloc {
	free(_sqlBuf); _sqlBuf = nil;
	if(_database) {
		sqlite3_close(_database);
		free(_database);
		_database = nil;
	}
	[super dealloc];
}

//【1】创建表格
//创建表格
//说明一下，id为表格的主键，必须有。
- (BOOL) createChannelsTable:(sqlite3*)db{
	NSString* sql = [NSString stringWithFormat:@"CREATE TABLE viewControllerData (id integer primary key,"
					 @"%@ integer, %@ integer, %@ text, %@ text, %@ integer, %@ BLOB, configLen integer, %@ BLOB, dataLen integer)",
					 naviControllerId, pushIndex, className, viewTitle, selectedIndex, configData, viewData];
	
    sqlite3_stmt *statement;
    if(sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil) != SQLITE_OK) {
        MCLog(@"Error: failed to prepare statement:create channels table");
        return NO;
    }
    int success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    if ( success != SQLITE_DONE) {
        MCLog(@"Error: failed to dehydrate:CREATE TABLE channels");
        return NO;
    }
    MCLog(@"Create table 'channels' successed.");
    return YES;
}

//【2】打开数据库，如果没有，那么创建一个
-(BOOL) open{
    NSString *path = [[MCFunction documentPath] stringByAppendingPathComponent:@"mydb.sql"];
    NSFileManager *fileManager = [NSFileManager defaultManager];
    BOOL find = [fileManager fileExistsAtPath:path];
	
    //找到数据库文件mydb.sql
    if (find) {
        MCLog(@"Database file have already existed.");
        if(sqlite3_open([path UTF8String], &_database) != SQLITE_OK) {
            sqlite3_close(_database);
            MCLog(@"Error: open database file.");
            return NO;
        }
        return YES;
    }
    if(sqlite3_open([path UTF8String], &_database) == SQLITE_OK) {
        [self createChannelsTable:_database];//在后面实现函数createChannelsTable
		
        return YES;
    } else {
        sqlite3_close(_database);
        MCLog(@"Error: open database file.");
        return NO;
    }
    return NO;
}

//【3】向表格中插入一条记录
//假设channle是一个数据结构体，保存了一条记录的内容。
- (BOOL) insertOneChannel:(NSDictionary*)channel{
    NSString* classNm = [channel objectForKey:className];
	NSString* title = [channel objectForKey:viewTitle];
	NSInteger selIndex = [[channel objectForKey:selectedIndex] intValue];
	NSData* config = [channel objectForKey:configData];
	NSData* data = [channel objectForKey:viewData];
	
	//问号代表未知的值，将在下面将值和字段关联。
	sprintf(_sqlBuf, "INSERT INTO viewControllerData (%s,%s,%s,%s,%s,%s,configLen,%s, dataLen) VALUES(?,?,?,?,?,?,?,?,?)",
			[naviControllerId UTF8String], [pushIndex UTF8String], [className UTF8String], [viewTitle UTF8String],
			[selectedIndex UTF8String], [configData UTF8String], [viewData UTF8String]);
	sqlite3_stmt *statement;
	
    int success = sqlite3_prepare_v2(_database, _sqlBuf, -1, &statement, NULL);
    if (success != SQLITE_OK) {
        MCLog(@"Error: failed to insert:channels");
        return NO;
    }
	
	//这里的数字0,1，2，3，4,5代表第几个问号
	int index = 1;
	sqlite3_bind_int (statement, index++, [[channel objectForKey:naviControllerId] intValue]);
	sqlite3_bind_int (statement, index++, [[channel objectForKey:pushIndex] intValue]);
    sqlite3_bind_text(statement, index++, [classNm UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(statement, index++, [title UTF8String], -1, SQLITE_TRANSIENT);
	sqlite3_bind_int (statement, index++, selIndex);
    sqlite3_bind_blob(statement, index++, [config bytes], [config length], SQLITE_TRANSIENT); 
	sqlite3_bind_int (statement, index++, [config length]);
	sqlite3_bind_blob(statement, index++, [data bytes], [data length], SQLITE_TRANSIENT); 
	sqlite3_bind_int (statement, index++, [data length]);
	
	
    success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    
    if (success == SQLITE_ERROR) {
        MCLog(@"Error: failed to insert into the database with message.");
        return NO;
    } 
	
	MCLog(@"Insert %@ view data", classNm);
    return YES;
}



//【4】数据库查询
//这里获取表格中所有的记录，放到数组fChannels中。
- (NSDictionary*) getChannels{
    sqlite3_stmt *statement = nil;
	sprintf(_sqlBuf, "SELECT DISTINCT %s FROM viewControllerData", [naviControllerId UTF8String]);
	
    if (sqlite3_prepare_v2(_database, _sqlBuf, -1, &statement, NULL) != SQLITE_OK) {
        MCLog(@"Error: failed to prepare statement with message:get channels.");
    }
	NSMutableDictionary *allDictionary = [NSMutableDictionary dictionary];
    //查询结果集中一条一条的遍历所有的记录，这里的数字对应的是列值。
    while (sqlite3_step(statement) == SQLITE_ROW) {
        char* naviId       = (char*)sqlite3_column_text(statement, 0);
        [allDictionary setObject:[NSMutableArray array] forKey:[NSString stringWithFormat:@"%s", naviId]];
    }
    sqlite3_finalize(statement);
	
	sprintf(_sqlBuf, "SELECT * FROM viewControllerData order by %s asc", [naviControllerId UTF8String]);
	
    if (sqlite3_prepare_v2(_database, _sqlBuf, -1, &statement, NULL) != SQLITE_OK) {
        MCLog(@"Error: failed to prepare statement with message:get channels.");
    }

    //查询结果集中一条一条的遍历所有的记录，这里的数字对应的是列值。
    while (sqlite3_step(statement) == SQLITE_ROW) {
		char* naviId       = (char*)sqlite3_column_text(statement, 1);
		NSMutableArray* array = [allDictionary objectForKey:[NSString stringWithFormat:@"%s", naviId]];
		NSMutableDictionary* item = [NSMutableDictionary dictionary];
		[array addObject:item];
		[item setObject:[NSNumber numberWithInt:sqlite3_column_int(statement, 2)]  forKey:pushIndex];
		[item setObject:[NSString stringWithFormat:@"%s", (char*)sqlite3_column_text(statement, 3)] forKey:className];
		[item setObject:[NSString stringWithFormat:@"%s", (char*)sqlite3_column_text(statement, 4)] forKey:viewTitle];
		[item setObject:[NSNumber numberWithInt:sqlite3_column_int(statement, 5)]  forKey:selectedIndex];
		int   configLen = sqlite3_column_int(statement, 7);
		Byte* config = (Byte*)sqlite3_column_blob(statement, 6);
		[item setObject:[NSData dataWithBytes:config length:configLen] forKey:configData];
		int   dataLen = sqlite3_column_int(statement, 9);
		Byte* data = (Byte*)sqlite3_column_blob(statement, 8);
		[item setObject:[NSData dataWithBytes:data length:dataLen] forKey:configData];
    }
    sqlite3_finalize(statement);
	
	return allDictionary;
}

//【5】清除所有表数据
- (BOOL) truncateChannels{
    sqlite3_stmt *statement = nil;

    if(sqlite3_prepare_v2(_database, "TRUNCATE TABLE viewControllerData", -1, &statement, nil) != SQLITE_OK) {
        MCLog(@"Error: failed to prepare statement:truncate viewControllerData table");
        return NO;
    }
    int success = sqlite3_step(statement);
    sqlite3_finalize(statement);
    if ( success != SQLITE_DONE) {
        MCLog(@"Error: failed to dehydrate:truncate viewControllerData table");
        return NO;
    }
    MCLog(@"truncate viewControllerData table successed.");
	return YES;
}
@end
